#!/usr/bin/env python3  /*设定脚本文件直接能执行*/
# -*- coding:utf-8 -*-   /*设置编码*/
"""
@Author: zhuo
@Software: PyCharm
@File: tExcelHelper.py
@Time: 2022/12/23 16:18
@Function： Excel操作模块
"""
import re
import xlrd
import openpyxl as op
from PySide6.QtWidgets import QTableWidgetItem
from openpyxl.utils import get_column_letter
from openpyxl.styles import Border, Side, Alignment, colors


class ctExcel:
    __m_property = {'name': 'ctExcel'}  # ctExcel类的私有属性

    # 初始化
    def __init__(self):
        self.book = op.Workbook()  # 创建excel对象
        self.sheet = self.book.active  # 创建sheet对象
        self.workbook = None  # xlrd打开excel文件

    # 使用openpyxl读取excel
    def f_readExcel(self, filepath):
        """
        function: 使用openpyxl读取excel
              in:
             out:
          return:
          others:
        """
        # 读取excel中的数据
        # 第一步：打开工作簿
        self.book = op.load_workbook(filepath)
        # 第二步：选取表单
        self.sheet = self.book['Sheet']
        # 第三步：读取数据
        # 参数 row:行  column：列
        # ce = self.sheet.cell(row=1, column=1)  # 读取第一行，第一列的数据
        # print(ce.value)

        # 去掉表头行
        rowCount = self.sheet.max_row - 1
        # 去掉序号行
        colCount = self.sheet.max_column - 1

        # 考虑一下如何做成通用类
        # 参数名称列表
        paraNameLst = []
        # 参数数据列表
        paraDataLst = []

        # 遍历单元格数据
        for cases in list(self.sheet.rows)[1:]:
            # 0列是序号 不要
            paraNameLst.append(cases[1].value)
            if cases[2].value is None:
                paraDataLst.append("")
            else:
                paraDataLst.append(cases[2].value)

        # 关闭工作薄
        self.book.close()
        return paraNameLst, paraDataLst

    # 在已有excel表中新建sheet页并添加多行数据项
    def f_createExcel(self, headLst, data, filepath):
        """
        function:
              in:
             out:
          return:
          others:
        """
        ws = self.book['Sheet']  # 创建子表
        ws.append(headLst)  # 添加表头

        for i in range(len(data[0])):
            d = data[0][i] + 1, data[1][i], data[2][i]
            ws.append(d)  # 每次写入一行

        # <editor-fold desc="设置单元格样式">
        # 预设单元格边框样式（style=""  边框样式（粗细、单双线等）；color=colors.XXX，边框颜色，xxx是颜色名称）
        border_set = Border(left=Side(style='thin', color=colors.BLACK),  # 左边框
                            right=Side(style='thin', color=colors.BLACK),  # 右边框
                            top=Side(style='thin', color=colors.BLACK),  # 上边框
                            bottom=Side(style='thin', color=colors.BLACK))  # 下边框
        # 预设单元格对齐方式格式
        align = Alignment(horizontal='center', vertical='center', wrapText=True)

        # 两层循环遍历所有有数据的单元格
        for i in range(1, ws.max_row + 1):
            for j in range(1, ws.max_column + 1):
                ws.cell(i, j).alignment = align  # 设置单元格居中
                ws.cell(i, j).border = border_set  # 设置单元格边框

        self.__f_columnWidthAdaptive()  # 列宽自适应
        # </editor-fold>
        self.book.save(filepath)
        return True

    # 读取Excel文件内容显示在QTableWidget
    def f_readExcelShowInQTableWidget(self, table_widget, file_path):
        """
        function:  Read Excel and Display in QTableWidget
              in:  table_widget: 要显示Excel内容的QTableWidget表格
                   file_path: 打开的Excel文件路径
             out:  None
          return:  None
          others:  读取Excel文件内容显示在QTableWidget
        """
        table_widget.clearContents()  # TableWidget内容清空
        table_widget.setRowCount(0)  # TableWidget，行数设置为0
        table_widget.setColumnCount(0)  # TableWidget，行数设置为0

        self.workbook = xlrd.open_workbook(file_path)  # xlrd打开excel文件

        # sheet_name = workbook.sheet_names()[0]  # 获取所有sheet
        sheet1 = self.workbook.sheet_by_index(0)  # 根据sheet索引或者名称获取sheet内容，sheet索引从0开始,获取sheet1

        data_row = []  # 初始化一个列表，填充每行数据
        for row in range(sheet1.nrows):
            data_row.append(sheet1.row_values(row))  # 将sheet中每行数据添加到列表中

        table_widget.setColumnCount(len(data_row[0]))  # 设置TableWidget列数为sheet内容列数

        cols = sheet1.col_values(0)  # 获取第1列内容

        for i in range(len(cols)):  # 获取整行和整列的值（数组）
            rowslist = sheet1.row_values(i)  # 获取excel每行内容
            row = table_widget.rowCount()
            table_widget.insertRow(row)  # 在TableWidget中添加行
            for j in range(len(rowslist)):
                new_item = QTableWidgetItem(str(rowslist[j]))
                table_widget.setItem(i, j, new_item)  # 把数据写入TableWidget中

        table_widget.resizeRowsToContents()  # 将行调整到跟内容大小相匹配
        table_widget.resizeColumnsToContents()  # 将列调整到跟内容大小相匹配

    # 从QTableWidget保存数据到Excel中
    def f_saveExcel(self, table_widget, file_path):
        """
        function:  List grouped by specified number
              in:  table：QTableWidget对象
             out:  file_path：Excel文件保存的路径
          return:  None
          others:  从QTableWidget保存数据到Excel中
        """
        row_count = table_widget.rowCount()  # 获取表格的行数
        column_count = table_widget.columnCount()  # 获取表格的列数
        labels_lst = []  # 表头列标题 列表

        for i in range(column_count):  # 表头列标题 列表填充数据
            labels_lst.append(table_widget.horizontalHeaderItem(i).text())  # 从QTableWidget获取列标题
        self.sheet.append(labels_lst)  # sheet添加列标题

        for row in range(row_count):
            for col in range(column_count):
                self.sheet.cell(row + 2, col + 1, table_widget.item(row, col).text())  # sheet单元格添加QTableWidget单元格的内容

        al = Alignment(horizontal='center', vertical='center', wrapText=True)  # 单元格的对齐格式
        thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                             top=Side(style='thin'), bottom=Side(style='thin'))  # 框线格式
        for row in range(row_count + 1):
            for col in range(1, column_count + 1):
                self.sheet.cell(row + 1, col).border = thin_border  # 单元格设置框线格式
                self.sheet.cell(row + 1, col).alignment = al  # 单元格设置对齐格式
        self.__f_columnWidthAdaptive()  # 列宽自适应
        self.book.save(file_path)  # Excel保存

    # 私有方法：列宽自适应
    def __f_columnWidthAdaptive(self):
        """
        function:  列宽自适应
              in:  None
             out:  None
          return:  None
          others:  Column width adaptive
        """
        # i = 0
        # col_width = []  # 创建列宽列表
        # for col in self.sheet.columns:  # 每列
        #     for j in range(len(col)):  # 每行
        #         if j == 0:
        #             col_width.append(len(str(col[j].value)))  # 数组增加一个元素
        #         else:
        #             if col_width[i] < len(str(col[j].value)):  # 判断宽度大小
        #                 col_width[i] = len(str(col[j].value))  # 获取每一列的内容的最大宽度
        #     i += 1
        # for i in range(len(col_width)):  # 设置列宽
        #     col_letter = get_column_letter(i + 1)  # 根据列的数字返回字母
        #     if col_width[i] > 0:
        #         self.sheet.column_dimensions[col_letter].width = col_width[i] + 5  # 宽度设置为长度+3
        width = 5  # 手动加宽的数值
        # 单元格列宽处理
        dims = {}
        for row in self.sheet.rows:
            for cell in row:
                if cell.value:
                    cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
                    dims[cell.column] = max((dims.get(cell.column, 0), cell_len))
            for col, value in dims.items():
                self.sheet.column_dimensions[get_column_letter(col)].width = value + width

    @staticmethod
    def write_excel(table_old, table_new, tab):
        """
        从QTableWidget中读取出数据，进行处理后显示在QTableWidget中
        :param table_old: 读取的QTableWidget
        :param table_new: 显示的QTableWidget
        :param tab: Tab切换页
        :return:
        """

        # 标题列
        labels = ["序号", "位号", "物料专用号", "物料描述", "工序", "规格值", "上限\n(%)", "下限\n(%)",
                  "ICT测试公差\n(电容/电压/\n电阻/开短路)", "(1)\nSMT AOI\n全测试\n(Y/N))",
                  "(2.1)\nICT\n全测试\n(Y/N))", "(2.2)\nFCT\n补试\n(Y/N))",
                  "(3)\n波峰焊后\nAOI全测试\n(Y/N))",
                  "(4)\nICT+FCT+SMT\nAOI+波峰焊后\nAOI\n(Y/N)",
                  "(5)目检\n(Y/N)", "(5.1)罩板\n(Y/N)",
                  "备注"]

        # 切换到Tab2
        tab.setCurrentIndex(1)
        # 设置列行数
        table_new.setColumnCount(17)
        table_new.setRowCount(1)
        # 写列标题
        for i in range(len(labels)):
            newitem = QTableWidgetItem(labels[i])
            table_new.setItem(0, i, newitem)
        # 将列调整到跟内容大小相匹配
        table_new.resizeColumnsToContents()
        table_new.resizeRowsToContents()

        # 位号：22 物料专用号：10 物料描述：11 工序：23 规格值： 上限： 下限：
        # 定义最初的列表old
        list_tag_old = []  # 位号：22
        list_special_old = []  # 物料专用号：10
        list_description_old = []  # 物料描述：11
        list_procedure_old = []  # 工序：23

        # 从tab1的表格中获取数据存在old列表中
        for i in range(table_old.rowCount()):
            list_tag_old.append(table_old.item(i, 21).text())
            list_special_old.append(table_old.item(i, 9).text())
            list_description_old.append(table_old.item(i, 10).text())
            list_procedure_old.append(table_old.item(i, 22).text())

        # 移除列标题栏
        list_tag_old.pop(0)
        list_special_old.pop(0)
        list_description_old.pop(0)
        list_procedure_old.pop(0)
        # 定义去除为空的位号，new列表
        list_tag_new = []
        list_special_new = []
        list_description_new = []
        list_procedure_new = []

        # 判断位号是否为空
        for i in range(len(list_tag_old) - 1):
            if list_tag_old[i] != "":
                list_tag_new.append(list_tag_old[i])
                list_special_new.append(list_special_old[i])
                list_description_new.append(list_description_old[i])
                list_procedure_new.append(list_procedure_old[i])

        # 定义最终的列表
        list_tag = []  # 位号
        list_special = []  # 物料专用号
        list_description = []  # 物料描述
        list_procedure = []  # 工序
        list_specs = []  # 规格
        list_upper = []  # 上限
        list_lower = []  # 下限

        # 添加item前需要先设置好行数
        table_new.setRowCount(len(list_tag) + 1)

        for i in range(len(list_tag)):
            # 在TableWidget中添加行
            # row = table_new.rowCount()
            # col = table_new.columnCount()
            # table_new.insertRow(row)
            # 把数据写入TableWidget中
            new_tag = QTableWidgetItem(str(list_tag[i]))
            new_special = QTableWidgetItem(str(list_special[i]))
            new_description = QTableWidgetItem(str(list_description[i]))
            new_procedure = QTableWidgetItem(str(list_procedure[i]))
            new_specs = QTableWidgetItem(str(list_specs[i]))
            new_upper = QTableWidgetItem(str(list_upper[i]))
            new_lower = QTableWidgetItem(str(list_lower[i]))
            table_new.setItem(i + 1, 0, QTableWidgetItem(str(i + 1)))
            table_new.setItem(i + 1, 1, new_tag)
            table_new.setItem(i + 1, 2, new_special)
            table_new.setItem(i + 1, 3, new_description)
            table_new.setItem(i + 1, 4, new_procedure)
            table_new.setItem(i + 1, 5, new_specs)
            table_new.setItem(i + 1, 6, new_upper)
            table_new.setItem(i + 1, 7, new_lower)

        # 将列调整到跟内容大小相匹配
        table_new.resizeColumnsToContents()
        table_new.resizeRowsToContents()

        # 表格表头的显示与隐藏
        # table_new.verticalHeader().setVisible(True)
        # table_new.horizontalHeader().setVisible(True)
